
package com.zsrt.jmj.teacher.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.zsrt.jmj.teacher.dao.TeacherDao;
import com.zsrt.jmj.teacher.entity.TeacherEntity;
import com.zsrt.jmj.utils.DBUtil;

public class TeacherDaoImpl implements TeacherDao{

	@Override
	public int add(TeacherEntity entity) throws Exception {
		int count=0;
		Connection connection = DBUtil.getConnect();
		String sql = "insert INTO jmj_teacher_t (teacher_name,salary,positional) VALUES(?,?,?)";
		PreparedStatement ps = connection.prepareStatement(sql );

		ps.setString(1, entity.getTeacherName());
		ps.setDouble(2, entity.getSalary());
		ps.setInt(3, entity.getPositional());
		
		count = ps.executeUpdate();
		DBUtil.closeResources(ps, connection);
		return count;
	}

	@Override
	public List<TeacherEntity> findAllTeachers() throws Exception{
		
		List<TeacherEntity> list  =new ArrayList<>();
		Connection connection = DBUtil.getConnect();
		String sql = "SELECT tea.ID AS id,tea.TEACHER_NAME AS teacherName,tea.SALARY AS SALARY,"
				+ "tea.POSITIONAL as POSITIONAL ,ch.CHILDREN_NAME as childrenName FROM `jmj_teacher_t` tea"
				+ " INNER JOIN jmj_children_t ch on ch.ID=tea.POSITIONAL order by tea.id desc";
		PreparedStatement ps = connection.prepareStatement(sql );
		
		ResultSet query = ps.executeQuery();
		while(query.next()){
			TeacherEntity entity = new TeacherEntity();
			entity.setId(query.getInt("id"));
			entity.setSalary(query.getDouble("SALARY"));
			entity.setTeacherName(query.getString("teacherName"));
			entity.setPositional(query.getInt("POSITIONAL"));
			entity.setPositionName(query.getString("childrenName"));
			
			list.add(entity);
		}
		DBUtil.closeResources(query, ps, connection);
		return list;
	}

	@Override
	public int updateById(TeacherEntity entity) throws SQLException {
		Connection connection = DBUtil.getConnect();
		String sql = " UPDATE jmj_teacher_t SET TEACHER_NAME=?,SALARY=?,POSITIONAL=? WHERE ID=?";
		PreparedStatement ps = connection.prepareStatement(sql);
		ps.setString(1, entity.getTeacherName());
		ps.setDouble(2, entity.getSalary());
		ps.setInt(3, entity.getPositional());
		ps.setInt(4, entity.getId());
		
		ps.executeUpdate();
		return 0;
	}

	@Override
	public TeacherEntity findTeacherById(int tId)throws SQLException {
		TeacherEntity entity = new TeacherEntity();
		
		Connection connection = DBUtil.getConnect();
		String sql = "select * from jmj_teacher_t where id=?";
		PreparedStatement ps = connection.prepareStatement(sql);
		ps.setInt(1, tId);
		ResultSet query = ps.executeQuery();
		while(query.next()){
			entity.setId(query.getInt(1));
			entity.setTeacherName(query.getString(2));
			entity.setSalary(query.getDouble(3));
			entity.setPositional(query.getInt(4));
		}
		return entity;
	}

	@Override
	public int deleteById(int cId) throws SQLException {
		int counter = 0;
		
		String sql  = "delete from jmj_teacher_t where ID=?";
		Connection connection = DBUtil.getConnect();
		
		PreparedStatement ps = null;
		ps = connection.prepareStatement(sql);
		ps.setInt(1, cId);
			
		counter = ps.executeUpdate();
		DBUtil.closeResources(ps, connection);
		return counter;
	}

	
}
